"""
Case Type   : GSC功能模块
Case Name   : gsc打开，线程池模式，某个库中某张表未进行缓存/已缓场景下，并发查询表，验证该库相应系统缓存信息变化
Create At   : 2022/4/27
Owner       : opentestcase024
Description :
    1、打开线程池、gsc开关;
    2、重启数据库，使参数生效;
    3、创建数据库;
    4、创建10个表;
    5、查询所有库并进行连接;
    6、表未缓存时，并发进行表查询，查看syscache占用情况;
Expect      :
    1、打开线程池、gsc开关;; 成功
    2、重启数据库，使参数生效; 重启成功
    3、创建数据库; 创建成功
    4、创建10个表; 创建成功
    5、查询所有库并进行连接; 连接成功
    6、表未缓存时，并发进行表查询，查看syscache占用情况; gsc最终缓存结果一致，如果不一致，各用户表缓存数据只有一条
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
    modified: 2023/2/7 modified by opentestcase024,验证某张表已缓存的情况下，系统缓存增加导致断言失败。
    原因：后台会自动进行一些系统表、视图的查询操作，导致数据库的系统缓存增加。
    优化用例，若系统缓存增加，判断缓存信息已经缓存的用户表数据只有一条。
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.db_name = 'db_gsc0019'
        self.circle_num = 10
        self.tb_num = 10
        self.tb_ex_name = 'tb_gsc0019_'
        self.tb_tmp_catalog = 'tb_gsc0019_tmp_catalog'
        self.tb_tmp_table = 'tb_gsc0019_tmp_table'
        self.init_para1 = self.com.show_param("enable_global_syscache")
        self.init_para2 = self.com.show_param('enable_thread_pool')

    def test_main(self):
        if self.init_para1 == "on" and self.init_para2 == "on":
            step_txt = '----线程池、gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False
        else:
            step_txt = '----step1:打开线程池、gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = self.sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)
            result = self.sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_thread_pool= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = self.sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True

        step_txt = f'----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'create database {self.db_name};'
        create_result = self.sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      create_result, '执行失败:' + step_txt)

        step_txt = f'----step4:创建{self.tb_num}个表; expect:创建成功----'
        self.log.info(step_txt)
        for i in range(self.tb_num):
            create_tb_sql = f"create table {self.tb_ex_name}{i} " \
                f"(c_integer integer,c_tinyint tinyint," \
                f"c_smallint smallint,c_binary_integer binary_integer," \
                f"c_bigint bigint,c_numeric numeric(10,4)," \
                f"c_number number(10,4),c_smallserial smallserial)"
            create_result = self.sh.execut_db_sql(create_tb_sql,
                                                  dbname=f'{self.db_name}')
            self.log.info(create_result)
            self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                          '执行失败:' + step_txt)

        step_txt = '----step5:查询所有库并进行连接; expect:连接成功----'
        self.log.info(step_txt)
        select_db_sql = "select datname from pg_database where datname " \
                        "not like('template%');"
        select_db_result = self.sh.execut_db_sql(select_db_sql)
        self.log.info(select_db_result)
        db_list = select_db_result.splitlines()[2:-1:1]
        for i in db_list:
            con_result = self.sh.execut_db_sql('select current_database();',
                                               dbname=f'{i}')
            self.log.info(con_result)
            self.assertIn(i, con_result, '执行失败:' + step_txt)

        step_txt = '----step6:表未缓存时，并发进行表查询，查看syscache占用情况; ' \
                   'expect:gsc最终缓存结果一致，如果不一致，各用户表缓存数据只有一条----'
        self.log.info(step_txt)
        connect_thread = []
        gsc_size = []
        for j in range(self.circle_num):
            connect_thread.append(ComThread(self.check_gsc, args=()))
            connect_thread[j].setDaemon(True)
            connect_thread[j].start()
        for j in range(self.circle_num):
            connect_thread[j].join(20)
            result = connect_thread[j].get_result()
            self.log.info(result)
            gsc_size.append(int(result.splitlines()[-2].strip()))
        self.log.info(gsc_size)
        if len(set(gsc_size)) > 1:
            for i in range(self.tb_num):
                recheck_sql = f"(select * from gs_gsc_table_detail() " \
                    f"where database_name = '{self.db_name}' " \
                    f"and relname='{self.tb_ex_name}{i}')"

                recheck_msg = self.sh.execut_db_sql(recheck_sql,
                                                    dbname=f'{self.db_name}')
                self.log.info(recheck_msg)
                self.assertIn('1 row', recheck_msg, '执行失败:' + step_txt)

    def check_gsc(self):
        check_sql = ''
        for i in range(self.tb_num):
            check_sql = check_sql + f"select * from {self.tb_ex_name}{i};" \
                f"select * from gs_gsc_dbstat_info() where " \
                f"database_name = '{self.db_name}';" \
                f"select usedsize from gs_gsc_memory_detail " \
                f"where db_id = (select cast((select oid from " \
                f"pg_database where datname = '{self.db_name}') as text));"
        check_result = self.sh.execut_db_sql(check_sql,
                                             dbname=f'{self.db_name}')
        return check_result

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----删除数据库 expect:删除成功----'
        self.log.info(step1_txt)
        drop_db = f'drop database {self.db_name}'
        drop_db_result = self.sh.execut_db_sql(drop_db)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = self.sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"enable_global_syscache "
                                              f"={self.init_para1}")
            self.msg2 = self.sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"enable_thread_pool "
                                              f"={self.init_para2}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = self.sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            status = self.sh.exec_cm_ctl(mode='query', param='-Cv')
            self.log.info(status)
            p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
            if 'Primary' in p_status and "cluster_state   : Normal" in status:
                self.log.info('---集群状态正常，无需恢复为初始状态---')
            else:
                recovery = self.sh.exec_cm_ctl(mode='switchover',
                                              param='-a')
                self.log.info(recovery)
                time.sleep(10)
            self.status_result = self.sh.get_db_cluster_status('status')

        self.log.info(f'----{os.path.basename(__file__)}:end----')
        flag = self.constant.DROP_DATABASE_SUCCESS
        self.assertIn(flag, drop_db_result, '执行失败:' + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + step_txt + self.step2_txt)
            self.assertTrue(self.msg2, '执行失败:' + step_txt + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + step_txt + self.step3_txt)
            self.assertTrue(self.status_result,
                            '执行失败:' + step_txt + self.step4_txt)
